package com.czlis.boe;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class ReadTmBoxProduct {

	public static String BOX_NAMES = "X49PT24N2,X49PEG1L2,X49P354V2,X49PEF3R2,X48P901H2,X49PT16N2,X4AP346H2,X49P6D391,X48PU13A1,X49PL5141,X49PT49H1,X48PU48R1,X49P6D991,X4AP336E1,X48P237G1,X48PP3581,X49PED251,X4AP3C8D4,X4APB44A4,X4APH7884,X4AP3D5R2,X48PL36Q2,X4APH79Q3,X4APH6323,X4APB37C3,X48PU22W1,X4APB25G1,X48PG1984,X49P6D844,X48PG18K3,X48PG26Q3,X48PL43F3,X49P6B9U3,X49P9A0T3,X49PL60U3,X49PEF9R3,X49PW1213,X49PEF5V3,X48PW33F3,X48PW35N4,X48PL4644,X49PT4634,X49P987K4,X49P6D8L4,X49P991B4,X48PP4553,X48PG11E2,X48PF42T5,X48PF60G5,X48PF40D6,X48PF54C2,X48PF39W3";

	public static void main(String[] args) throws Exception {
		String url = "jdbc:oracle:thin:@118.178.86.19:1521/orcl";
		String username = "czlis";
		String password = "junaiTAO1!";
		int index = 1;
		for (String productName : BOX_NAMES.split(",")) {
			System.out.print("Read GLASS：" + productName + " 序号：" + (index++) + "  ");
			try (Connection connection = DriverManager.getConnection(url, username, password);
					PreparedStatement statement = connection
							.prepareStatement("select p.fromboxname fromboxname,boxname from ct_slimifretproduct p where p.productname=?")) {
				statement.setString(1, productName);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					String fromboxname = resultSet.getString("fromboxname");
					String boxname = resultSet.getString("boxname");
					System.out.print(fromboxname + " - " + boxname);
					
					Statement stamt = connection.createStatement();
					String sql = "select opertime,info from sys_log l where l.oper = '创建箱号' and l.info like '%产品%"+productName+"%箱号为%成功。原箱号为%' and l.operdate>='20240901' order by id asc";
					ResultSet rs = stamt.executeQuery(sql);
					System.out.print(" 移箱时间 -->");
					while (rs.next()) {
						String opertime = rs.getString("opertime");
						System.out.print(opertime + "  ");
					}
					System.out.print("   |   ");
					
					sql = "select opertime,info from sys_log l where l.oper = '移出天马产品' and l.info like '%产品%"+productName+"%从箱号%' and l.operdate>='20240901' order by id asc";
					rs = stamt.executeQuery(sql);
					System.out.print(" 移出时间 -->");
					while (rs.next()) {
						String opertime = rs.getString("opertime");
						String info = rs.getString("info");
						System.out.print(opertime + " " + info + " / ");
					}
					System.out.print("   |   ");
					
					sql = "select outtime from ct_slimifretbox b where b.boxname='"+fromboxname+"'";
					rs = stamt.executeQuery(sql);
					System.out.print(" 出库时间 -->");
					while (rs.next()) {
						String opertime = rs.getString("outtime");
						System.out.print(opertime + "   ");
					}
					System.out.print("   &   ");
					rs.close();
					stamt.close();
					
				}
			}
			System.out.println();
		}
	}

}
